package com.roadmap.repo.db;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.roadmap.common.BaseEntity;
import com.roadmap.common.RdcConst;
import com.roadmap.common.RdcLogger;
import com.roadmap.common.BaseEnum.ActionType;
import com.roadmap.common.BaseEnum.QueryType;
import com.roadmap.exception.InvalidActionException;
import com.roadmap.exception.InvalidQueryException;
import com.roadmap.exception.InvalidValueException;
import com.roadmap.exception.NullObjectException;
import com.roadmap.exception.ServerException;

/**
 * this class is used to perform basic actions directly relating to database. it
 * wraps the Hibernate methods
 */
public class DatabaseUtility implements IDatabaseUtility {
	private final static Logger _log = RdcLogger
			.getLogger(DatabaseUtility.class);

	/** save an entity to database */
	public void save(BaseEntity entity) throws ServerException,
			NullObjectException, InvalidActionException {
		insertUpdateDelete(entity, ActionType.CREATE);
	}

	/** update an entity in database */
	public void update(BaseEntity entity) throws ServerException,
			NullObjectException, InvalidActionException {
		insertUpdateDelete(entity, ActionType.UPDATE);
	}

	/** delete an entity from database */
	public void delete(BaseEntity entity) throws ServerException,
			NullObjectException, InvalidActionException {
		insertUpdateDelete(entity, ActionType.DELETE);
	}

	/** save or update several entities at one time */
	public void saveOrUpdateEntities(Vector<BaseEntity> entities)
			throws ServerException, NullObjectException {
		if (entities == null) {
			NullObjectException noe = new NullObjectException(
					"the entity can't be NULL");
			_log.error(noe);
			throw noe;
		}

		Session session = getSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			for (int i = 0; i < entities.size(); i++) {
				BaseEntity entity = entities.get(i);
				if (entity != null) {
					session.saveOrUpdate(entity);
				}
			}
			tx.commit();
		}
		catch (HibernateException ex) {
			_log.error(ex);

			ServerException se = new ServerException(
					"error happened when saving or updating data in database");
			se.initCause(ex);
			try {
				tx.rollback();
			}
			catch (NullPointerException he) {
				se.initCause(he);
			}
			catch (HibernateException he) {
				se.initCause(he);
			}

			_log.error(se);
			throw se;
		}
		finally {
			session.close();
		}
	}

	/** save or update several entities at one time */
	public void saveOrUpdateEntity(BaseEntity entity) throws ServerException,
			NullObjectException {
		if (entity == null) {
			NullObjectException noe = new NullObjectException(
					"the entity can't be NULL");
			_log.error(noe);
			throw noe;
		}

		Session session = getSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(entity);
			tx.commit();
		}
		catch (HibernateException ex) {
			_log.error(ex);

			ServerException se = new ServerException(
					"error happened when saving or updating data in database");
			se.initCause(ex);
			try {
				tx.rollback();
			}
			catch (NullPointerException he) {
				se.initCause(he);
			}
			catch (HibernateException he) {
				se.initCause(he);
			}

			_log.error(se);
			throw se;
		}
		finally {
			session.close();
		}
	}

	private void insertUpdateDelete(BaseEntity entity, ActionType action)
			throws ServerException, NullObjectException, InvalidActionException {
		if (entity == null) {
			NullObjectException noe = new NullObjectException();
			_log.error("the entity can't be NULL", noe);
			throw noe;
		}

		Session session = getSession();
		Transaction tx = session.beginTransaction();
		try {
			if (action == ActionType.CREATE) {
				session.save(entity);
				if (_log.isDebugEnabled()) {
					_log.debug(entity.getClass().getSimpleName() + " "
							+ entity.getId() + " was saved successfully");
				}
			}
			else {
				// check the entity id
				if (entity.getId() == null) {
					NullObjectException noe = new NullObjectException(
							"the entity id can't be NULL");
					_log.error(noe);
					throw noe;
				}

				if (action == ActionType.UPDATE) {
					session.update(entity);
					if (_log.isDebugEnabled()) {
						_log.debug(entity.getClass().getSimpleName() + " "
								+ entity.getId() + " was updated successfully");
					}
				}
				else if (action == ActionType.DELETE) {
					session.delete(entity);
					if (_log.isDebugEnabled()) {
						_log.debug(entity.getClass().getSimpleName() + " "
								+ entity.getId() + " was deleted successfully");
					}
				}
			}
			tx.commit();
		}
		catch (HibernateException ex) {
			InvalidActionException se = new InvalidActionException();
			se.initCause(ex);
			try {
				tx.rollback();
			}
			catch (Exception he) {
				se.initCause(he);
			}

			_log.error("error happened when updating data in database.", se);
			throw se;
		}
		finally {
			session.close();
		}
	}

	/**
	 * get an entity from database by its ID. return null if nothing is found
	 * 
	 * @param clazz :
	 *            should be the type of entities which are mapped to tables in
	 *            database
	 * @param id :
	 *            the id of entity you want to get from database
	 */
	public BaseEntity getEntityById(Class clazz, String id)
			throws ServerException, NullObjectException, InvalidValueException,
			InvalidQueryException {
		if (clazz == null || RdcConst.getEntityNameClazz(clazz.getSimpleName()) == null
				|| isEntityIDInvalid(id)) {
			StringBuffer msg = new StringBuffer(clazz.getName());
			msg
					.append(" is not mapped to any table in database, or id is invalid");
			InvalidValueException ive = new InvalidValueException(msg
					.toString());
			_log.error(ive);
			throw ive;
		}

		Session session = getSession();
		return (BaseEntity) session.get(clazz, id);
	}

	/**
	 * get a set of entities by a specific query criteria
	 */
	public List getEntitiesByCriteria(Class clazz, String criteria)
			throws ServerException, NullObjectException, InvalidValueException,
			InvalidQueryException {
		if (clazz == null || RdcConst.getEntityNameClazz(clazz.getSimpleName()) == null
				|| criteria == null || criteria.trim().length() == 0) {
			StringBuffer msg = new StringBuffer(clazz.getName());
			msg
					.append(" is not mapped to any table in database, or query criteria ");
			msg.append(criteria);
			msg.append("  is invalid.");
			InvalidValueException ive = new InvalidValueException(msg
					.toString());
			_log.error(ive);
			throw ive;
		}

		String simpleName = clazz.getSimpleName();
		StringBuffer hsql = new StringBuffer("from ");
		hsql.append(simpleName);
		hsql.append(" where ");
		hsql.append(criteria);

		List list = queryByHSQL(hsql.toString());
		if (list != null && _log.isDebugEnabled()) {
			_log.debug("number of returned results: " + list.size());
		}
		return list;
	}

	/** search in database by using HSQL and return a list of entities */
	public List queryByHSQL(String hsql) throws ServerException,
			NullObjectException, InvalidQueryException {
		return query(hsql, QueryType.HSQL);
	}

	/** search in database by using HSQL and return a list of entities */
	public List executeHQLQuery(String hql, Object[] params)
			throws ServerException {
		return executeHQLQuery(hql, params, 0);
	}

	/** search in database by using HSQL and return a list of entities */
	public List executeHQLQuery(String hql, Object[] params, int maxResults)
			throws ServerException {
		Session session = getSession();
		try {
			Query query = session.createQuery(hql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					query.setParameter(i, params[i]);
				}
			}

			if (maxResults > 0) {
				query.setMaxResults(maxResults);
			}

			if (_log.isDebugEnabled()) {
				_log.debug("starting to query by " + query.getQueryString());
			}

			return query.list();
		}
		catch (HibernateException e) {
			_log.error(
					"error happened when querying data in database by SQL statement: "
							+ hql, e);
			throw e;
		}
		finally {
			session.close();
		}
	}

	/** search in database by using native SQL and return a list of entities */
	public List queryBySQL(String sql) throws ServerException,
			NullObjectException, InvalidQueryException {
		return query(sql, QueryType.SQL);
	}

	private List query(String sql, QueryType query) throws ServerException,
			NullObjectException, InvalidQueryException {
		if (sql == null) {
			NullObjectException noe = new NullObjectException(
					"SQL statements can't be NULL");
			_log.error(noe);
			throw noe;
		}

		Session session = getSession();
		try {
			if (_log.isDebugEnabled()) {
				_log.debug("starting to execute query: " + sql);
			}
			if (query == QueryType.HSQL) {
				return session.createQuery(sql).list();
			}
			else if (query == QueryType.SQL) {
				return session.createSQLQuery(sql).list();
			}
			return null;
		}
		catch (HibernateException hex) {
			_log.error(
					"error happened when querying data in database by SQL statement: "
							+ sql, hex);
			throw hex;
		}
		finally {
			session.close();
		}
	}

	public Session getSession() throws ServerException {
		Session session = null;
		try {
			session = SessionPool.getInstance().getSession();
		}
		catch (Exception use) {
			ServerException se = new ServerException(use);
			_log.error("can't get an available session", se);
			throw se;
		}

		if (session == null) {
			ServerException se = new ServerException(
					"can't get an available session. the connections reach the top quantity.");
			_log.error(se);
			throw se;
		}

		return session;
	}

	/**
	 * check if the entity's id is INVALID or not. if INVALID, return true, or
	 * else return false
	 */
	protected boolean isEntityIDInvalid(String id) {
		if (id != null && id.trim().length() > 0) {
			return false;
		}
		else {
			_log.error("given id is invalid");
			return true;
		}
	}

	public RdcQuery createNativeSQLQuery(String sql) throws ServerException {
		return RdcQuery.getInstance(getSession(), sql);
	}

	public Object getUniqueEntity(String hql, Object[] params)
			throws ServerException {
		Session session = getSession();

		try {
			Query query = session.createQuery(hql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					query.setParameter(i, params[i]);
				}
			}
			return query.uniqueResult();
		}
		catch (HibernateException e) {
			_log.error("failed to get the unique result", e);
			throw e;
		}
		finally {
			session.close();
		}

	}

	public List executeHQLQeury(String hql, String alias, String[] paramList)
			throws ServerException {
		Session session = getSession();
		try {
			Query query = session.createQuery(hql);
			if (alias != null && paramList != null) {
				query.setParameterList(alias, paramList);
			}

			if (_log.isDebugEnabled()) {
				_log.debug("starting to query by " + query.getQueryString());
			}

			return query.list();
		}
		catch (HibernateException e) {
			_log.error("error happened when querying data in database", e);
			throw e;
		}
		finally {
			session.close();
		}
	}

	public List executeHQLQeury(String hql, Map<String, Object> params)
			throws ServerException {
		Session session = getSession();
		try {
			Query query = session.createQuery(hql);
			if (params != null && !params.isEmpty()) {
				Set<String> keys = params.keySet();
				Iterator<String> iter = keys.iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					Object value = params.get(key);
					if (value instanceof Object[]) {
						query.setParameterList(key, (Object[]) value);
					}
					else if (value instanceof List) {
						query.setParameterList(key, (List) value);
					}
					else {
						query.setParameter(key, value);
					}
				}
			}

			if (_log.isDebugEnabled()) {
				_log.debug("starting to query by " + query.getQueryString());
			}

			return query.list();
		}
		catch (HibernateException e) {
			_log.error(
					"error happened when querying data in database by SQL statement: "
							+ hql, e);
			throw e;
		}
		finally {
			session.close();
		}
	}

	public void executeHQLUpdate(String hql, Object[] params)
			throws NullObjectException, ServerException {
		if (hql == null) {
			NullObjectException noe = new NullObjectException(
					"HQL statements can't be NULL");
			_log.error(noe);
			throw noe;
		}
		else if(_log.isDebugEnabled()) {
			_log.debug("going to execute the update hql: " + hql);
		}

		Session session = getSession();
		Transaction tx = null;
		try {
			Query query = session.createQuery(hql);
			tx = session.beginTransaction();
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					query.setParameter(i, params[i]);
				}
			}
			query.executeUpdate();
			tx.commit();
		}
		catch (HibernateException e) {
			_log.error(e);
			if (tx != null) {
				try {
					tx.rollback();
				}
				catch (HibernateException ex) {
					_log.error(ex);
				}
			}
		}
		finally {
			session.close();
		}
	}
}
